﻿package com.farvin.gis;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.trs.infra.persistent.db.ConnectPasswordEncrypter;

/**
 * 数据库工具类
 * @author FLF
 */
public class DbUtils {
	
	private static DataSource dataSource = null;
	
	/**
	 * 获取数据源
	 * @return
	 */
	public static DataSource getDataSource() {
		if(dataSource==null){
			System.out.println("初始化数据库连接池：开始  ");
	        BasicDataSource ds = new BasicDataSource();
	        ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
	        String[] connectionInfo = getDbConnectionInfo();
	        ds.setUsername(connectionInfo[0]);
	        ds.setPassword(connectionInfo[1]);
	        ds.setUrl(connectionInfo[2]);
	        dataSource = ds;
	        System.out.println("初始化数据库连接池：结束");
		}
        return dataSource;
    }
	
	/**
	 * 获取数据库连接
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException{
		DataSource ds = getDataSource();
		Connection conn = ds.getConnection();
		return conn;
	}
	
	/**
	 * 关闭各种数据库连接对象
	 * @param conn
	 * @param stmt
	 * @param rs
	 */
	public static void close(Connection conn,Statement stmt,ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				rs = null;
			}
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				stmt = null;
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally{
				conn = null;
			}
		}
	}
	
	/**
	 * 解析数据库配置文件，获取连接数据库的用户、密码、连接地址
	 * @return 字符串数组:用户、密码、连接地址
	 */
	private static String[] getDbConnectionInfo(){
		String[] connectionInfo = new String[3];
		InputStream dbConfigStream = DbUtils.class.getResourceAsStream("/trsconfig/domain/config.xml");
		//System.out.println(dbConfigStream);
		SAXReader reader = new SAXReader();
		try {
			Document document = reader.read(dbConfigStream);
			Element oracleElement = (Element) document.selectSingleNode("/plugin/extension/DBConnections/DBConnect[@name='Oracle']");
			String username = oracleElement.attribute("connectionUser").getValue();
			String password = ConnectPasswordEncrypter.deEncrypt(oracleElement.attribute("connectionPassword").getValue());
			String url = oracleElement.attribute("connectionURL").getValue();
			connectionInfo[0] = username;
			connectionInfo[1] = password;
			connectionInfo[2] = url;
		} catch (DocumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connectionInfo;
	}
	
	private static void test1() throws SQLException{
		Connection conn = getConnection();
		String sql = "select count(*) from WCMCONFIG t";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			System.out.println(rs.getInt(1));
		}
		close(conn, stmt, rs);
	}
	
	public static void main(String[] args) throws SQLException {
		test1();
		System.out.println("================");
		test1();
	}
}
